import pymysql
import xlrd
from site1.methods.ga import ga_execute


class vrp_solution:

    def __init__(self, input_info):

        self.type = 1

        self.database = pymysql.connect(host="localhost", user="root", password="zzh200086", db="dachuang")
        self.cursor = self.database.cursor()
        self.data_dict = input_info
        self.get_id_weight()
        self.get_distace_matrix()
        self.input_type()

    def input_type(self):
        # 固车 单型号
        if self.data_dict['CarIsNotChangeAble'] and self.data_dict['OneTypeCar']:
            self.type = 1
        # 固车 双型号
        if self.data_dict['CarIsNotChangeAble'] and self.data_dict['TwoTypeCar']:
            self.type = 2
        # 变车 单型号
        if self.data_dict['CarIsChangeAble'] and self.data_dict['OneTypeCar']:
            self.type = 3
            self.data_dict['CarNumSet'] = len(self.data_dict['NodesInfo'])/6
        # 变车 双型号
        if self.data_dict['CarIsChangeAble'] and self.data_dict['TwoTypeCar']:
            self.data_dict['CarNumSet'] = len(self.data_dict['NodesInfo']) / 6
            self.type = 4

    def get_distace_matrix(self):
        sql = f"select start,end,distance from distances"
        self.cursor.execute(sql)
        sql_infos = self.cursor.fetchall()
        n = len(self.data_dict['NodesInfo']) + 1
        self.data_dict['distance_matrix'] = [[0] * n for i in range(n)]
        s = []
        e = []
        d = []
        for sql_info in sql_infos:
            s.append(sql_info[0])
            e.append(sql_info[1])
            d.append(sql_info[2])

        for i in range(0, len(s)):
            self.data_dict['distance_matrix'][s[i]][e[i]] = d[i]

    def get_id_weight(self):
        sql = "select id,weight from destination"
        self.cursor.execute(sql)
        sql_infos = self.cursor.fetchall()
        sites_wt = []
        sites_id = []
        for sql_info in sql_infos:
            sites_id.append(sql_info[0])
            sites_wt.append(sql_info[1])
        for i in range(0, len(sites_id)):
            loc = (sites_id[i], sites_wt[i])
            self.data_dict['NodesInfo'].append(loc)

    def decode_route_info(self, route_data):
        detail_info = []
        for each_route in route_data:
            each = []
            for i in range(0, len(each_route)):
                if each_route[i] == 0:
                    sql = f"select longitude,latitude,weight,center_name from center where id = {each_route[i]} "
                    self.cursor.execute(sql)
                    data = self.cursor.fetchone()
                    each.append((data[0], data[1], data[2], data[3]))
                else:
                    sql = f"select longitude,latitude,weight,dst_name from destination where id = {each_route[i]} "
                    self.cursor.execute(sql)
                    data = self.cursor.fetchone()
                    each.append((data[0], data[1], data[2], data[3]))
            detail_info.append(each)
        return detail_info

    def vrp_execute(self):
        execute = ga_execute.ga_algorithm(self.data_dict)
        return_data, route_time, route_load, route_length = execute.ga_main()
        return self.type, self.decode_route_info(return_data), route_time, route_load, route_length
